﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTQC20VData : BaseFunction
    {
        
        public JLTQC20VData()
            : base()
        {

        }
        public JLTQC20VData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetQC20V(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "QC20V";
            string ART = dwABPOS["ART"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            try
            {
                
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select to_number(A.QOKORDER) as qcorder,A.QOKNEUD,A.QOKERLDAT, "
                    + " A.QOKRESULT AS TOTRESULT,TRIM(QOKPNRKRS)||'-'||TO_NUMBER(QOKPLFDNR) as qpplan, "
                    + " A.QOKARTST,TRIM(A.QOKART)AS ART,TRIM(A.QOKFARBE) AS COLOR, "
                    + " TRIM(QOKAUFKRS)||'-'||TO_NUMBER(A.QOKAUFLFD)||'-'||TO_NUMBER(QOKAUFPOS) "
                    + " ||'-'||TO_NUMBER(QOKTP) AS ORDERNO,TO_NUMBER(A.QOKPKUNDNR) AS CUSTOMER, "
                    + " (select substr(sddaten,52,10) from sddat where sdsa = '0734' and substr(sddaten,1,1)='A' "
                    + " and substr(sdkey,3,1) = E.VAAALTERNAT ) as STRNAME, "
                    + " (select substr(sddaten,2,20)from sddat where sdsa = '0178' and substr(sdkey,24,1)='V' "
                    + "  and substr(sddaten,1,1)='A' and substr(sdkey,3,3) = VAAARTGRP ) as ARTNAME, "
                    + "  (select nvl(TRIM(T2.TXTKTEXT),e.vaabez) "
                    + " from txtind T1,TXTKON T2 where T1.txtitxtart = 'VAD' "
                    + " AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                    + " AND E.vaakoll||trim(E.vaaart) = trim(T1.TXTISIND) "
                    + " and e.vaamandant = t1.txtimandant) as descr,e.vaabrtvon*0.01 as valwid, "
                    + " e.vaabrtbis*0.01-1||'-'||(e.vaabrtbis*0.01) as wid,c.qpmbez, "
                    + " (select substr(sddaten,2,40)from sddat where sdsa = '0477' and substr(sddaten,1,1)='A' "
                    + "  and substr(sdkey,3,15) = c.qpmnorm) as std, "
                    + " (select substr(sddaten,2,40)from sddat where sdsa = '0477' and substr(sddaten,1,1)='A' "
                    + "  and substr(sdkey,3,15) = b.qoeverur) as actstd,(select DISTINCT STRAGG(TRIM(T2.TXTKTEXT)) "
                    + " from txtind T1,TXTKON T2 where T1.txtitxtart = 'QOE' "
                    + " AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                    + " AND TRIM(T1.TXTISIND) = B.QOEORDER||B.QOESTEP and t1.txtimandant = b.qoemandant) AS REMARK, "
                    + " B.QOEMEEH, decode(b.qoeugkz,'S',DECODE(B.QOEOGKZ,'O','>='||((B.QOENSWERT+B.QOENSUG)*0.001), "
                    + " (B.QOENSWERT+B.QOENSUG)*0.001||'~'||((B.QOENSWERT+B.QOENSOG)*0.001)),'O',DECODE(B.QOEOGKZ,'S', "
                    + " '<='||((B.QOENSWERT+B.QOENSOG)*0.001),' ','<='||((B.QOENSWERT+B.QOENSOG)*0.001), "
                    + " (B.QOENSWERT+B.QOENSUG)*0.001||'~'||((B.QOENSWERT+B.QOENSOG)*0.001)), "
                    + " decode(B.QOEMERKM,'WG  ','NORMAL',decode(substr(b.qoemerkm,1,3),'AZO','FORBID', "
                    + " DECODE(B.QOENSWERT,0,' ',(B.QOENSWERT+B.QOENSUG)*0.001||'~'||((B.QOENSWERT+B.QOENSOG)*0.001))))) AS REQ, "
                    + " decode(b.qoenumkz,'J',decode(B.QOEPDATUM,0,' ',to_char(B.QOEMWERT*0.001,'99990.99')), "
                    + " (select MAX(X.QOVAIWERT) from qovalue X where X.QOVMANDANT = B.QOEMANDANT "
                    + " AND X.QOVORDER = B.QOEORDER AND X.QOVSTEP = B.QOESTEP)) AS VA, "
                    + " B.QOEPDATUM,B.QOEPBENID,B.QOEFRBENID,B.QOESTATUS,B.QOERESULT,d.qosident,d.qoscomment,d.qosproddate "
                    + " FROM QORDER A,VAART E, QOSTEP B,QPMERKM C,qsample d "
                    + " WHERE A.QOKMANDANT = B.QOEMANDANT AND A.QOKORDER = B.QOEORDER "
                    + " AND A.QOKMANDANT = E.VAAMANDANT AND A.QOKART = E.VAADART "
                    + " AND b.qoemandant = c.qpmmandant AND B.QOEMERKM = C.QPMMERKM "
                    + " and a.qokmandant = d.qosmandant(+) and a.qokorder = d.qosorder(+) "
                    + " AND (A.QOKNEUD >= " + FROMDNDATE + " AND  A.QOKNEUD <= " + ENDDNDATE + ") "
                    + " AND A.QOKART LIKE '" + ART + "%' "
                    + " ORDER BY 1,2 "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        /*foreach (DataColumn cm in Result.Tables[TableName].Columns)
                        {
                            string cn = cm.ColumnName;
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[cn].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[cn] = value;
                        
                        }*/
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STRNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STRNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DESCR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DESCR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["QPMBEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["QPMBEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STD"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STD"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ACTSTD"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ACTSTD"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["VA"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["VA"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["QOSIDENT"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["QOSIDENT"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["QOSCOMMENT"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["QOSCOMMENT"] = value;

                        value = dw["TOTRESULT"].ToString().Trim();
                        if (value == "N")
                        {
                            dw["TOTRESULT"] = "不合格";
                        }
                        else if (value == "O")
                        {
                            dw["TOTRESULT"] = "合格";
                        }
                        else 
                        {
                            dw["TOTRESULT"] = "未测试";
                        }
                        value = dw["REQ"].ToString().Trim();
                        if (value == "NORMAL")
                        {
                            dw["REQ"] = "正常";
                        }
                        else if (value == "FORBID")
                        {
                            dw["REQ"] = "禁含";
                        }
                        value = dw["QOESTATUS"].ToString().Trim();
                        if (value == "ER")
                        {
                            dw["QOESTATUS"] = "完成";
                        }
                        value = dw["QOERESULT"].ToString().Trim();
                        if (value == "N")
                        {
                            dw["QOERESULT"] = "不合格";
                        }
                        else if (value == "O")
                        {
                            dw["QOERESULT"] = "合格";
                        }
                        else
                        {
                            dw["QOERESULT"] = "未测试";
                        }
                        /*
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["TYPE"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["TYPE"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STRNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STRNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DESCR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DESCR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["QOSIDENT"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["QOSIDENT"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["A05"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["A05"] = value;
                         * */
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildJLTQC20VData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetQC20V(dwUI);
                setReturn.Merge(setResult);  
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}